package com.sm.dao;

import com.sm.entity.Attendance;
import com.sm.entity.AttendanceRec;
import com.sm.entity.AttendanceTotal;
import jdk.Exported;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;

@Repository
public interface AttendanceDao {

    /**
     * @param uid 员工ID
     * @param month 月份
     * @return 该员工当月考勤记录
     * @throws Exception 所有异常
     */
    @Select("select * from attendance where uid = #{uid} and month = #{month}")
    @Results({
            @Result(property = "uid",column = "uid"),
            @Result(property = "userInfo",column = "uid",one = @One(select = "com.sm.dao.UserDao.findByUid"))
    })
    public List<Attendance> findAttendanceByUid(@Param("uid")Integer uid,@Param("month")Integer month)throws Exception;

    /**
     * @param uid 员工ID
     * @param month 月份
     * @return 员工当月总工作时间记录
     * @throws Exception 所有异常
     */
    @Select("select * from attendance_total where uid = #{uid} and month = #{month} ")
    @Results(id = "ATotalMap",value = {
            @Result(property = "overtimea",column = "overtime_a"),
            @Result(property = "overtimeb",column = "overtime_b"),
            @Result(property = "overtimec",column = "overtime_c"),
            @Result(property = "userInfo",column = "uid",one = @One(select = "com.sm.dao.UserDao.findByUid"))
    })
    public AttendanceTotal findATotalByUid(@Param("uid")Integer uid,@Param("month")Integer month)throws Exception;

    /**
     * @param month 月份
     * @return 所有员工当月总工作时间记录
     * @throws Exception 所有异常
     */
    @Select("select * from attendance_total where month = #{month}")
    @ResultMap("ATotalMap")
    public List<AttendanceTotal> findAllATotal(Integer month)throws Exception;

    /**
     * @param dateStr 当天日期字符串格式
     * @return 当日打卡比例
     * @throws Exception 所有异常
     */
    @Select("select * from attendance_rec where date = #{dateStr}")
    public AttendanceRec findAttendanceCount(String dateStr)throws Exception;

    /**
     * @return 最近7天的考勤比例
     * @throws Exception 所有异常
     */
    @Select("select * from attendance_rec where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date")
    public List<AttendanceRec> findAtCounts()throws Exception;

    /**
     * @param dateStr 当天日期字符串格式
     * @return 考勤比例表是否更新了
     * @throws Exception 所有异常
     */
    @Select("select * from attendance_rec where date = #{dateStr}")
    public Integer findAtRecId(String dateStr)throws Exception;

    /**
     * @param uid  员工ID
     * @param dateStr 当天日期字符串格式
     * @return 当日是否已经打过卡了
     * @throws Exception 所有异常
     */
    @Select("select aid from attendance where uid = #{uid} and date = #{dateStr}")
    public Integer findAtAid(@Param("uid") Integer uid,@Param("dateStr")String dateStr)throws Exception;

    /**
     * @param uid 员工ID
     * @param dateStr 当天日期字符串格式
     * @return 员工当日情况
     * @throws Exception 所有异常
     */
    @Select("select status from attendance where uid = #{uid} and date = #{dateStr}")
    public Integer findAtStatus(@Param("uid") Integer uid,@Param("dateStr")String dateStr)throws Exception;

    /**
     * @param uid 员工ID
     * @param dateStr 当天日期字符串格式
     * @return 当日是否下班签到了
     * @throws Exception 所有异常
     */
    @Select("select flag from attendance where uid = #{uid} and date = #{dateStr}")
    public Integer findAtFlag(@Param("uid") Integer uid,@Param("dateStr")String dateStr)throws Exception;

    /**
     * @param uid 员工ID
     * @param dateStr 当天日期字符串格式
     * @return 当日是否加班了
     * @throws Exception 所有异常
     */
    @Select("select sign from attendance where uid = #{uid} and date = #{dateStr}")
    public Integer findAtSign(@Param("uid") Integer uid,@Param("dateStr")String dateStr)throws Exception;

    /**
     * @param uid 员工ID
     * @param dateStr 当天日期字符串格式
     * @return 当天工作时间 分钟为单位
     * @throws Exception 所有异常
     */
    @Select("select TIMESTAMPDIFF(MINUTE,starttime,endtime ) from attendance where uid = #{uid} and date = #{dateStr};")
    public Integer findWorkMinute(@Param("uid") Integer uid,@Param("dateStr")String dateStr)throws Exception;

    /**
     * @param attendance 每日打卡 新建考勤表
     * @throws Exception 所有异常
     */
    @Insert("insert into attendance(uid,date,starttime,month,status) values(#{uid},#{date},#{starttime},#{month},#{status})")
    public void addAttendance(Attendance attendance)throws Exception;

    /**
     * @param uid 员工ID 新添加员工时一次性添加三条
     * @param month 月份
     * @throws Exception 所有异常
     */
    @Insert("insert into attendance_total(uid,month) values(#{uid},#{month})")
    public void addAttendanceTotal(@Param("uid")Integer uid,@Param("month")Integer month)throws Exception;

    /**
     * 创建当天的考勤比例表
     * @param dateStr 当天日期
     * @throws Exception 所有异常
     */
    @Insert("insert into attendance_rec(date,usercount) values(#{dateStr},(select count(uid) from user where status > 0)) ")
    public void addAttendanceRec(String dateStr)throws Exception;

    /**
     * 下班打卡
     * @param attendance 员工考勤对象 下班的时间 状态
     * @throws Exception 所有异常
     */
    @Update("update attendance set endtime = #{endtime} , flag = #{flag} where uid = #{uid} and date = #{dateStr}")
    public void updateAttendance(Attendance attendance)throws Exception;

    /**
     * 下班打卡 更新总表
     * @param uid 员工ID
     * @param month 月份
     * @param time 工作时间 分钟为单位
     * @throws Exception 所有异常
     */
    @Update("update attendance_total set worktime = #{time} where uid = #{uid} and month = #{month}")
    public void updateATotalTime(@Param("uid") Integer uid,@Param("month")Integer month,@Param("time")Integer time)throws Exception;

    /**
     * 下班打卡 更新总表1.5倍工作时间
     * @param uid 员工ID
     * @param month 月份
     * @param time 工作时间 分钟为单位
     * @throws Exception 所有异常
     */
    @Update("update attendance_total set overtime_a = #{time} where uid = #{uid} and month = #{month}")
    public void updateATotalOverTimeA(@Param("uid") Integer uid,@Param("month")Integer month,@Param("time")Integer time)throws Exception;

    /**
     * 下班打卡 更新总表2倍工作时间
     * @param uid 员工ID
     * @param month 月份
     * @param time 工作时间 分钟为单位
     * @throws Exception 所有异常
     */
    @Update("update attendance_total set overtime_b = #{time} where uid = #{uid} and month = #{month}")
    public void updateATotalOverTimeB(@Param("uid") Integer uid,@Param("month")Integer month,@Param("time")Integer time)throws Exception;

    /**
     * 下班打卡 更新总表3倍工作时间
     * @param uid 员工ID
     * @param month 月份
     * @param time 工作时间 分钟为单位
     * @throws Exception 所有异常
     */
    @Update("update attendance_total set overtime_c = #{time} where uid = #{uid} and month = #{month}")
    public void updateATotalOverTimeC(@Param("uid") Integer uid,@Param("month")Integer month,@Param("time")Integer time)throws Exception;

    /**
     * 添加加班
     * @param attendance 员工考勤对象
     * @throws Exception 所有异常
     */
    @Update("update attendance set ostime = #{ostime} ,oetime = #{oetime} ,sign = #{sign} where uid =#{uid} and date = #{dateStr}")
    public void updateOverTime(Attendance attendance)throws Exception;

    /**
     * 更新当天是否加班
     * @param uid 员工ID
     * @param dateStr 日期格式
     * @param sign 加班标志
     * @throws Exception 所有异常
     */
    @Update("update attendance set sign = #{sign} where uid = #{uid} and date = #{dateStr}")
    public void updateSign(@Param("uid") Integer uid,@Param("dateStr")String dateStr,@Param("sign")Integer sign)throws Exception;

    /**
     * 更新考勤比例表打卡人数
     * @param dateStr 日期格式
     * @throws Exception 所有异常
     */
    @Update("update attendance_rec set atcount = atcount+1 where date = #{dateStr}")
    public void updateAtCount(String dateStr)throws Exception;

    /**
     * 置零上上个月的考勤总表
     * @param month 月份
     * @throws Exception 所有异常
     */
    @Update("update attendance_total set worktime = 0, overtime_a = 0, overtime_b = 0 , overtime_c = 0  where month = #{month}")
    public void deleteAttendanceTotal(Integer month)throws Exception;

    /**
     * 删除上上个月的考勤总表
     * @param month 月份
     * @throws Exception 所有异常
     */
    @Delete("delete from attendance_total where status = #{month}")
    public void clearAttendanceTotal(Integer month)throws Exception;

    /**
     * 删除员工个人的考勤签到表
     * @param uid 员工ID
     * @param month 月份
     * @throws Exception 所有异常
     */
    @Delete("delete from attendance where uid = #{uid} and month = #{month}")
    public void deleteAttendance(@Param("uid") Integer uid,@Param("month")Integer month)throws Exception;

    //删除所有del月份的考勤签到表
    @Delete("delete from attendance where  month = #{month}")
    public void deleteAllAttendance(Integer month)throws Exception;

}
